package com.hy.dao;

import com.hy.bean.User;
import com.hy.utils.DBManager;
import com.hy.utils.DateUtil;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.util.List;
import java.util.Map;

public class AdminDao {
    private QueryRunner qr = new QueryRunner();

    public List<User> selectLogin(String userName, String password) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * from ums_user where phone  = ? and password = ?  and tombstone = '0' and personnel_status='1'";
        BasicRowProcessor brp = new BasicRowProcessor(new GenerousBeanProcessor());
        List<User> query = qr.query(conn, sql, new BeanListHandler<>(User.class, brp), userName, password);
        DbUtils.close(conn);
        return query;
    }

    public List<Map<String, Object>> selectCard(Integer page, Integer limit) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select c.*,u.user_name,u.img from card c INNER JOIN ums_user u on u.id = c.user_id where review_status = '0' and c.tombstone = '0' and u.tombstone = '0' limit ?,?";
        List<Map<String, Object>> mapList = qr.query(conn, sql, new MapListHandler(), page * limit, limit);
        DbUtils.close(conn);
        return mapList;
    }

    public boolean updateCard(Integer id, String status, Integer userId) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "update card set review_status = ?,update_time = ?,update_id = ? where id = ?";
        int update = qr.update(conn, sql, status, DateUtil.datetime(), userId, id);
        DbUtils.close(conn);
        return update > 0 ? true : false;
    }

    public Long selectCardCount() throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select count(1) from card c INNER JOIN ums_user u on u.id = c.user_id where review_status = '0' and c.tombstone = '0' and u.tombstone = '0'";
        Long query = qr.query(conn, sql, new ScalarHandler<Long>());
        DbUtils.close(conn);
        return query;
    }

    public List<Map<String, Object>> selectReportCard(Integer page, Integer limit) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "SELECT a.*,u.user_name,c.card_content,c.title,report_reason\n" +
                "FROM accusation a\n" +
                "LEFT JOIN card c ON a.card_id = c.id\n" +
                "LEFT JOIN ums_user u ON a.create_id = u.id\n" +
                "LEFT JOIN report r ON a.report_id = r.id\n" +
                "where a.tombstone = '0' and c.tombstone = '0' and a.status = '0' limit ?,?";
        List<Map<String, Object>> mapList = qr.query(conn, sql, new MapListHandler(), page * limit, limit);
        DbUtils.close(conn);
        return mapList;
    }

    public List<Map<String, Object>> selectReportUser(Integer page, Integer limit) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "SELECT a.*,u.user_name,u1.user_name as user_name1,report_reason\n" +
                "FROM accusation a\n" +
                "LEFT JOIN ums_user u ON a.user_id = u.id\n" +
                "LEFT JOIN ums_user u1 ON a.create_id = u1.id\n" +
                "LEFT JOIN report r ON a.report_id = r.id\n" +
                "where a.tombstone = '0' and u.tombstone = '0' and a.status = '0' limit ?,?";
        List<Map<String, Object>> mapList = qr.query(conn, sql, new MapListHandler(), page * limit, limit);
        DbUtils.close(conn);
        return mapList;
    }

    public List<Map<String, Object>> selectReportComment(Integer page, Integer limit) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "SELECT a.*,u.user_name,c.comment_content,report_reason\n" +
                "FROM accusation a\n" +
                "LEFT JOIN `comment` c ON a.comment_id = c.id\n" +
                "LEFT JOIN ums_user u ON a.create_id = u.id\n" +
                "LEFT JOIN report r ON a.report_id = r.id\n" +
                "where a.tombstone = '0' and c.tombstone = '0' and a.status = '0' limit ?,?";
        List<Map<String, Object>> mapList = qr.query(conn, sql, new MapListHandler(), page * limit, limit);
        DbUtils.close(conn);
        return mapList;
    }

    public Long selectReportCardCount() throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "SELECT count(1)\n" +
                "FROM accusation a\n" +
                "LEFT JOIN card c ON a.card_id = c.id\n" +
                "LEFT JOIN report r ON a.report_id = r.id\n" +
                "where a.tombstone = '0' and c.tombstone = '0' and a.status = '0'";
        Long query = qr.query(conn, sql, new ScalarHandler<Long>());
        DbUtils.close(conn);
        return query;
    }

    public Long selectReportUserCount() throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "SELECT count(1)\n" +
                "FROM accusation a\n" +
                "LEFT JOIN ums_user u ON a.user_id = u.id\n" +
                "LEFT JOIN report r ON a.report_id = r.id\n" +
                "where a.tombstone = '0' and u.tombstone = '0' and a.status = '0'";
        Long query = qr.query(conn, sql, new ScalarHandler<Long>());
        DbUtils.close(conn);
        return query;
    }

    public Long selectReportCommentCount() throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "SELECT count(1)\n" +
                "FROM accusation a\n" +
                "LEFT JOIN `comment` c ON a.comment_id = c.id\n" +
                "LEFT JOIN report r ON a.report_id = r.id\n" +
                "where a.tombstone = '0' and c.tombstone = '0' and a.status = '0'";
        Long query = qr.query(conn, sql, new ScalarHandler<Long>());
        DbUtils.close(conn);
        return query;
    }

    public boolean updateReport(Integer id, String status, Integer userId) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "update accusation set status = ?,update_time = ?,update_id = ? where id = ?";
        int update = qr.update(conn, sql, status, DateUtil.datetime(), userId, id);
        DbUtils.close(conn);
        return update > 0 ? true : false;
    }

    public List<Map<String,Object>> selectId(Connection conn,Integer id) throws Exception {
        String sql = "SELECT a.* from accusation a\n" +
                "LEFT JOIN `comment` cm ON a.comment_id = cm.id\n" +
                "LEFT JOIN `card` c ON a.card_id = c.id\n" +
                "LEFT JOIN `ums_user` u ON a.user_id = u.id\n" +
                "LEFT JOIN report r ON a.report_id = r.id\n" +
                "where a.tombstone = '0' and a.status = '0' and a.id = ?";
        List<Map<String, Object>> query = qr.query(conn, sql, new MapListHandler(), id);
        return query;
    }

    public boolean updateIdComment(Connection conn,Integer id,Integer userId) throws Exception {
        String sql = "update comment set tombstone = '1',update_time = ?,update_id = ? where id = ?";
        int update = qr.update(conn, sql, DateUtil.datetime(), userId, id);
        return update > 0 ? true : false;
    }

    public boolean updateIdCard(Connection conn,Integer id,Integer userId) throws Exception {
        String sql = "update card set tombstone = '1',update_time = ?,update_id = ? where id = ?";
        int update = qr.update(conn, sql, DateUtil.datetime(), userId, id);
        return update > 0 ? true : false;
    }

    public List<Map<String,Object>> selectIdComment(Integer id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select id from `comment` where card_id = ?";
        List<Map<String,Object>> query = qr.query(conn, sql, new MapListHandler(), id);
        DbUtils.close(conn);
        return query;
    }

    public List<Map<String,Object>> selectUserStatus(Integer id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select user_status from ums_user where id = ?";
        List<Map<String,Object>> query = qr.query(conn, sql, new MapListHandler(), id);
        DbUtils.close(conn);
        return query;
    }

    public boolean updateUserStatus(Connection conn,Integer id,Integer userId,String status) throws Exception {
        String sql = "update ums_user set user_status = ?,update_time = ?,update_id = ? where id = ?";
        int update = qr.update(conn, sql,status, DateUtil.datetime(), userId, id);
        return update > 0 ? true : false;
    }
}
